package com.papper.common.service.tripartite;

import com.google.common.collect.Lists;
import com.papper.common.bean.biz.BizDatasourceConfig;
import com.papper.common.bean.sys.SysConfig;
import com.papper.common.domain.DBColumn;
import com.papper.common.domain.TableColumn;
import com.papper.common.service.sys.SysConfigService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author:LaJiao
 * @date 2022年03月25日
 * @time 15时10
 * @Description:
 */
@Service("JDBCService")
public class JDBCService {
    private static final Logger logger = LoggerFactory.getLogger(JDBCService.class);
    @Autowired
    SysConfigService sysConfigService;

    public List<TableColumn> oracleTable(String driver, String url, String username, String password, String datebase) {
        try {
            // 加载MySql的驱动类
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            logger.error("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        }

        StringBuffer buffer = new StringBuffer();
        buffer.append(" select TABLE_NAME,COMMENTS  from all_tab_comments WHERE OWNER='").append(datebase).append("'");

        Connection con  = null;
        Statement  stmt = null;
        ResultSet  rs   = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            rs = stmt.executeQuery(buffer.toString());
            List<TableColumn> rtn = new ArrayList<TableColumn>();
            while (rs.next()) {
                TableColumn temp = new TableColumn();
                temp.setName(rs.getString("TABLE_NAME"));
                temp.setComment(rs.getString("COMMENTS"));
                rtn.add(temp);
            }
            return rtn;
        } catch (SQLException se) {
            logger.error("数据库连接失败！");
            se.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public List<DBColumn> oracle(String driver, String url, String username, String password, String datebase, String tabelname) {
        try {
            // 加载MySql的驱动类
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            logger.error("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        }

        StringBuffer buffer = new StringBuffer();
        buffer.append(" SELECT COLUMN_NAME,COMMENTS from all_col_comments ");
        buffer.append(" WHERE OWNER = '").append(datebase).append("'");
        buffer.append(" AND table_name = '").append(tabelname).append("'");

        // 连接MySql数据库，用户名和密码都是root
        Connection con  = null;
        Statement  stmt = null;
        ResultSet  rs   = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            rs = stmt.executeQuery(buffer.toString());
            List<DBColumn> rtn = new ArrayList<DBColumn>();
            while (rs.next()) {
                DBColumn temp = new DBColumn();
                temp.setName(rs.getString("COLUMN_NAME"));
                temp.setType("varchar");
                temp.setComment(rs.getString("COMMENTS"));
                rtn.add(temp);
            }
            return rtn;
        } catch (SQLException se) {
            logger.error("数据库连接失败！");
            se.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public List<TableColumn> mysqlTable(BizDatasourceConfig datasourceConfig) {
        return mysqlTable(datasourceConfig.getDriverClass(), datasourceConfig.getHost(), datasourceConfig.getUsername(), datasourceConfig.getPassword(), datasourceConfig.getDbName());
    }

    public List<TableColumn> mysqlTable(String driver, String url, String username, String password, String datebase) {
        try {
            // 加载MySql的驱动类
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            logger.error("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        }

        StringBuffer buffer = new StringBuffer();
        buffer.append(" SELECT TABLES.TABLE_NAME as name, TABLES.TABLE_COMMENT as comment ");
        buffer.append(" FROM information_schema.TABLES ");
        buffer.append(" where  TABLES.TABLE_SCHEMA = '").append(datebase).append("'");

        // 连接MySql数据库，用户名和密码都是root
        Connection con  = null;
        Statement  stmt = null;
        ResultSet  rs   = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            rs = stmt.executeQuery(buffer.toString());
            List<TableColumn> rtn = Lists.newArrayList();
            // 过滤表信息
            List<SysConfig> sysConfigs = sysConfigService.findChild("DbFilter");
            while (rs.next()) {
                TableColumn temp = new TableColumn();
                temp.setName(rs.getString("name"));
                temp.setComment(rs.getString("comment"));
                if (sysConfigs != null && !sysConfigs.isEmpty()) {
                    boolean canAdd    = true;
                    String  tableName = rs.getString("name");
                    for (SysConfig sysConfig : sysConfigs) {
                        if (tableName.startsWith(sysConfig.getCodeKey())) {
                            canAdd = false;
                            break;
                        }
                    }
                    if (canAdd) {
                        rtn.add(temp);
                    }
                } else {
                    rtn.add(temp);
                }
            }
            return rtn;
        } catch (SQLException se) {
            logger.error("数据库连接失败！");
            se.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public List<DBColumn> mysql(BizDatasourceConfig datasourceConfig, String tabelname) {
        return mysql(datasourceConfig.getDriverClass(), datasourceConfig.getHost(), datasourceConfig.getUsername(), datasourceConfig.getPassword(), datasourceConfig.getDbName(), tabelname);
    }

    public List<DBColumn> mysql(String driver, String url, String username, String password, String datebase, String tabelname) {
        try {
            // 加载MySql的驱动类
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        }

        StringBuffer buffer = new StringBuffer();
        buffer.append("SELECT COLUMN_NAME as name,DATA_TYPE as type, CHARACTER_MAXIMUM_LENGTH as length, " + "COLUMN_COMMENT as comment, COLUMN_KEY as key1, EXTRA as extra1");
        buffer.append(" FROM information_schema.COLUMNS");
        buffer.append(" WHERE COLUMNS.TABLE_SCHEMA = '").append(datebase).append("'");
        buffer.append(" AND COLUMNS.TABLE_NAME = '").append(tabelname).append("'");
        buffer.append(" ORDER BY ORDINAL_POSITION ASC ");

        System.out.println(buffer.toString());

        // 连接MySql数据库，用户名和密码都是root
        Connection con  = null;
        Statement  stmt = null;
        ResultSet  rs   = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            stmt = con.createStatement();
            rs = stmt.executeQuery(buffer.toString());
            List<DBColumn> rtn = new ArrayList<DBColumn>();
            while (rs.next()) {
                DBColumn temp = new DBColumn();
                temp.setName(rs.getString("name"));
                temp.setType(rs.getString("type"));
                temp.setLength(rs.getString("length"));
                temp.setComment(rs.getString("comment"));
                temp.setKey(rs.getString("key1"));
                temp.setExtra(rs.getString("extra1"));
                rtn.add(temp);
            }
            return rtn;
        } catch (SQLException se) {
            System.out.println("数据库连接失败！");
            se.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}
